Grokking Database Fundamentals for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction to Database Systems
Course Overview
Overview of Databases
Core Components of Database
Database Architectures
Relational Vs. Non-relational Databases
Data Storage in DBMS
Data Storage Fundamentals
File Organization in DBMS
Data Compression and Encoding
Quiz
Indexing in DBMS
Introduction to Database Indexes
Single Level Indexing
Tree-Based Indexing Techniques
Hash-Based Indexing
Bloom Filters
Quiz
Transaction Management
Understanding the Transaction
Atomicity
Consistency
Isolation
Durability
Quiz
Concurrency Control
Understanding the Concurrency Control
Optimistic vs. Pessimistic Concurrency Control
Lock-Based Concurrency Control
Timestamp-Based Concurrency Control
Multiversion Concurrency Control (MVCC)
Quiz
Fundamentals of Distributed Databases
Partitioning in Databases
Partitioning Types
Sharding in Databases
Distributed Query Processing
Quiz
Replication in Databases
Replication Topologies
Understanding the Consistency Models in Databases
The CAP Theorem and Its Implications
Quiz
Understanding the Consensus
Consensus algorithms
Leader Election Strategies
Quiz
Understanding the Fault Tolerance
Logging Mechanisms in Databases
Checkpointing and Recovery Basics
Coordinated Recovery in Distributed Systems
High Availability Through Replication and Failover
Ensuring Data Resilience: Redundancy, Mirroring, and Recovery
Quiz
Introduction to NoSQL
Key-Value Stores
Document-Oriented Databases
Column-Family and Wide-Column Stores
Graph Databases
Quiz
Quora System Design & Database Design
TikTok System Design & Database Design
Airbnb System Design & Database Design
Snapchat System Design & Database Design
Optimistic vs. Pessimistic Concurrency Control

Optimistic Concurrency Control (OCC)

OCC assumes that conflicts are rare and allows transactions to execute without restrictions until they are ready to commit. At the commit stage, the system checks for conflicts.

How OCC Works

Image
  1. Transaction Execution: A transaction reads data and makes changes locally without acquiring locks.
  2. Validation Phase: Before committing, the database checks if any conflicts occurred (e.g., if another transaction modified the same data).
  3. Commit or Rollback:
    • If no conflicts are detected, the transaction commits.
    • If conflicts are detected, the transaction rolls back and retries.

Advantages of OCC

  • No Lock Overhead: Transactions do not acquire locks, reducing system overhead.
  • Better Performance: Ideal for systems with low contention, as most transactions succeed without conflicts.
  • Higher Scalability: Works well in distributed systems where locking can be expensive.

Disadvantages of OCC

  • Rollback Costs: Transactions may have to restart from scratch if conflicts are detected during validation.
  • Inefficient in High Contention: Frequent conflicts lead to excessive rollbacks, reducing performance.

Use Cases

  • Systems with low contention where conflicts are infrequent.
  • Read-heavy workloads, such as reporting or analytics.
  • Distributed systems, where locking is expensive.

Real-World Example

  1. User A and User B both read the same product inventory.
  2. User A updates the inventory count and submits the transaction.
  3. When User B tries to update the inventory, the system validates and detects the conflict, prompting User B to retry.

Pessimistic Concurrency Control (PCC)

PCC assumes that conflicts are likely and prevents them by acquiring locks on data as soon as a transaction accesses it.

How PCC Works

Image
  1. Lock Acquisition: When a transaction reads or writes data, it acquires a lock to ensure no other transaction can access the same data concurrently.
  2. Conflict Prevention: Locks prevent other transactions from accessing the same data until the current transaction completes.
  3. Commit or Release: Once the transaction commits or rolls back, the locks are released.

Advantages of PCC

  • Prevents Conflicts: Transactions are serialized, ensuring no interference.
  • No Rollbacks: Conflicts are avoided upfront, eliminating the need for restarts.
  • Consistency Guarantees: Ideal for critical systems where correctness is paramount.

Disadvantages of PCC

  • Lock Overhead: Locking introduces significant overhead, especially in systems with many transactions.
  • Deadlocks: Multiple transactions can block each other, requiring deadlock detection and resolution.
  • Reduced Concurrency: Locking limits parallelism, impacting performance in high-concurrency systems.

Use Cases:

  • Systems with high contention, where conflicts are frequent.
  • Write-heavy workloads, such as banking or financial systems.
  • Critical applications that require strict consistency, like inventory management.

Real-World Example

  1. User A starts editing a product's inventory, acquiring a lock on the product row.
  2. User B tries to access the same product but is blocked until User A completes the transaction and releases the lock.

Key Differences Between OCC and PCC

AspectOptimistic Concurrency Control (OCC)Pessimistic Concurrency Control (PCC)
AssumptionConflicts are rareConflicts are frequent
LocksNo locks usedLocks are used
PerformanceHigh in low-contention scenariosHigh in high-contention scenarios
RollbackRollbacks occur if conflicts are detectedNo rollbacks due to conflicts
OverheadLow locking overheadHigh locking overhead
Use CaseRead-heavy or low-contention workloadsWrite-heavy or high-contention workloads

Choosing Between OCC and PCC

CriteriaUse OCCUse PCC
System ContentionLow contentionHigh contention
Transaction TypeRead-heavyWrite-heavy
Failure ToleranceHigh (rollbacks acceptable)Low (strict consistency required)
System TypeDistributed systems or systems with low conflictsCentralized systems with frequent conflicts
Understanding the Concurrency Control
Lock-Based Concurrency Control
Mark as Completed

On this page

Optimistic Concurrency Control (OCC)

How OCC Works

Advantages of OCC

Disadvantages of OCC

Use Cases

Real-World Example

Pessimistic Concurrency Control (PCC)

How PCC Works

Advantages of PCC

Disadvantages of PCC

Use Cases:

Real-World Example

Key Differences Between OCC and PCC

Choosing Between OCC and PCC